# Copyright (C) 2008 Paul Hummer
# 
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

__licence__ = 'GPLv2'
__copyright__ = '2008, Paul Hummer'
__author__ = 'Paul Hummer <paul@ironlionsoftware.no.spam.com>'

import os
from pysqlite2 import dbapi2 as sqlite

class DatabaseException(Exception): pass

class Database(object):

    connection = None
        
    def __init__(self, filename=False):

        if filename:
            self.connect(filename)

    def connect(self, filename):

        if self.connection:
            self.connection.close()
            self.cursor = None

        create = False
        if not os.path.exists(filename):
            create = True

        self.connection = sqlite.connect(filename)
        self.connection.row_factory = sqlite.Row
        self.cursor = self.connection.cursor()

        if create:
            self._create_database()

    def _create_database(self):

        if not self.connection:
            raise DatabaseException('No connection made!')

        self.cursor.execute('''
            CREATE TABLE newsfeed(
              id INT,
              url TEXT,
              title TEXT,
              description TEXT,
              PRIMARY KEY(id));
            ''')

        self.cursor.execute('''
            CREATE TABLE newsentry(
              id INT,
              feed INT,
              url TEXT,
              title TEXT,
              description TEXT,
              datetime INT,
              isread BOOLEAN,
              PRIMARY KEY(id));
            ''')

        self.cursor.execute('''
            CREATE TABLE photoalbum(
              id INT,
              title TEXT,
              description TEXT,
              datetime INT, 
              PRIMARY KEY(id));
            ''')

        self.cursor.execute('''
            CREATE TABLE photoimage(
              id INT,
              album INT,
              filename TEXT,
              thumb VARCHAR(32),
              title TEXT,
              description TEXT,
              datetime INT,
              PRIMARY KEY(id));
            ''')

        self.cursor.execute('''
            CREATE TABLE musicalbum(
              id INT,
              artist TEXT,
              title TEXT,
              year INT,
              genre TEXT,
              PRIMARY KEY(id));
            ''')

        self.cursor.execute('''
            CREATE TABLE musictrack(
              id INT,
              album INT,
              filename TEXT,
              title TEXT,
              tracknumber INT,
              rating INT,
              length INT,
              bitrate INT,
              comment TEXT,
              lyrics TEXT,
              PRIMARY KEY(id));
            ''')

        self.cursor.execute('''
            CREATE TABLE musicplaylist(
              id INT,
              title TEXT,
              PRIMARY KEY(id));
            ''')

        self.cursor.execute('''
            CREATE TABLE musicplaylisttrack(
              id INT,
              track INT,
              playlist INT,
              PRIMARY KEY(id));
            ''')

        self.cursor.execute('''
            CREATE TABLE videofile(
              id INT,
              filename TEXT,
              thumb CHAR(32),
              type TEXT,
              PRIMARY KEY(id));
            ''')

        self.cursor.execute('''
            CREATE TABLE videoseries(
              id INT,
              title TEXT,
              actor_1 TEXT,
              actor_2 TEXT,
              actor_3 TEXT,
              actor_4 TEXT,
              actor_5 TEXT,
              writer_1 TEXT,
              writer_2 TEXT,
              director_1 TEXT,
              director_2 TEXT,
              PRIMARY KEY(id));
            ''')

        self.cursor.execute('''
            CREATE TABLE videometa(
              id INT,
              file INT,
              series INT,
              season INT,
              episode INT,
              runtime INT,
              genres TEXT,
              year INT,
              plot TEXT,
              PRIMARY KEY(id));
            ''')

db = Database(filename='db')
